﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
GO


CREATE FUNCTION [dbo].[bhf_NemoTranslateGet](
	@FieldId		INT,
	@Record			INT,
	@Language		NCHAR(5),
	@DefaultValue	NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

	RETURN
		COALESCE(
			(SELECT TOP 1
				translate
			FROM
				[bht_NemoTranslates]
			WHERE
				field_id		= @FieldId
				AND record_id	= @Record
				AND lang_code	= @Language), @DefaultValue)

END
GO

CREATE FUNCTION [dbo].[bhf_NemoTranslateGetByName](
	@ColumnName	VARCHAR(100),
	@TableId			INT,
	@Record			INT,
	@Language		NCHAR(5),
	@DefaultValue	NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

	RETURN
		COALESCE(
			(SELECT TOP 1
				translate
			FROM
				[bht_NemoTranslates]
			WHERE
				field_id		= (SELECT TOP 1 id FROM sTree WHERE name = @ColumnName AND parent = @TableId)
				AND record_id	= @Record
				AND lang_code	= @Language), @DefaultValue)

END
GO
CREATE PROCEDURE [dbo].[bhp_NemoTranslateGetLanguages]
AS
BEGIN
	
	SELECT DISTINCT lang_code FROM [bht_NemoTranslates]

END

GO

CREATE PROCEDURE [dbo].[bhp_NemoTranslateGetOryginal] (
	@GroupId	INT,
	@FieldId	INT,
	@RecordId	INT
)
AS
BEGIN
	
	DECLARE
		@Q			NVARCHAR(1000),
		@ColName	NVARCHAR(100)
		
	SELECT
		@ColName = name
	FROM
		sTree 
	WHERE
		id = @FieldId
		
	SET @Q = 'SELECT ' + @ColName + ' FROM T' + CAST(@GroupId AS NVARCHAR(10)) + ' WHERE idx = ' + CAST(@RecordId AS NVARCHAR(10))
	EXEC sp_executesql @Q

END

GO

CREATE PROCEDURE [dbo].[bhp_NemoTranslateGet] (
	@GroupId	INT,
	@FieldId	INT,
	@RecordId	INT,
	@Language	NCHAR(5)
)
AS
BEGIN
	
	DECLARE
		@Val		NVARCHAR(MAX)
		
	SELECT @Val = dbo.[bhf_NemoTranslateGet](@FieldId,@RecordId,@Language,'')
	
	SELECT @Val

END

GO